
insert overwrite table jms_dm.dm_end_sender_report_sum_dt partition(dt)
select 
       date_time
     , pick_regional_code   -- 取件_大区code
     , pick_regional_name   -- 取件_大区name
     , pick_agent_code      -- 取件_代理区编码
     , pick_agent_name      -- 取件_代理区名称
     , pick_fran_code       -- 取件_加盟商
     , pick_fran_name       -- 取件_加盟商
     , pick_network_code    -- 取件网点code
     , pick_network_name    -- 取件网点名称     
     ,null as business_name        -- 品牌
     ,null as post_site_code       -- 门店编码
     ,null as post_site_name       -- 门店name
     ,null as post_site_address    -- 门店地址
     ,null as customer_code        -- 客户编号code
     ,null as customer_name        -- 客户编号名称  
     ,order_source_code as order_source_code             --订单来源code
     ,order_source_name as order_source_name             --订单来源名称 
     
     , count(distinct post_site_code) as post_site_num        -- 寄件门店数量
     , count(distinct customer_code ) as customer_num         -- 寄件客户数量

     
     , count(waybill_id) as bill_num             -- 订单量	 	 	 
     , sum(if(dt=to_date(pick_time),1,0)) as today_pick_num       -- 当日揽收量	 
     , sum(if(datediff(to_date(pick_time),dt)=1,1,0)) as nextday_pick_num     -- 次日揽收量	 
     , sum(if(pick_time is not null ,1,0)) as total_pick_num       -- 总揽收量	 
     , sum(if(cancel_time is not null ,1,0)) as cancel_num           -- 取消量	 
     , sum(if(pick_time is not null  and pick_time>best_pick_time_end,1,0)) as pick_time_end_num    -- 超时未取件
     , 1 as type                 -- 1网点,2门店,3订单维度	 
     , category_id
     , dt 
from jms_dm.dm_end_sender_report_detail_dt t 
where dt between date_add('{{ execution_date  | cst_ds }}', -4) and '{{ execution_date  | cst_ds }}'  
group by 
       date_time
     , pick_regional_code   -- 取件_大区code
     , pick_regional_name   -- 取件_大区name
     , pick_agent_code      -- 取件_代理区编码
     , pick_agent_name      -- 取件_代理区名称
     , pick_fran_code       -- 取件_加盟商
     , pick_fran_name       -- 取件_加盟商
     , pick_network_code    -- 取件网点code
     , pick_network_name    -- 取件网点名称
     , dt
     , category_id
     ,order_source_code       --订单来源code
     ,order_source_name       --订单来源名称 
union all 

select 
       date_time
     , pick_regional_code   -- 取件_大区code
     , pick_regional_name   -- 取件_大区name
     , pick_agent_code      -- 取件_代理区编码
     , pick_agent_name      -- 取件_代理区名称
     , pick_fran_code       -- 取件_加盟商
     , pick_fran_name       -- 取件_加盟商
     , pick_network_code    -- 取件网点code
     , pick_network_name    -- 取件网点名称     
     , business_name        -- 品牌
     , post_site_code       -- 门店编码
     , post_site_name       -- 门店name
     , post_site_address    -- 门店地址
     , customer_code        -- 客户编号code
     , customer_name        -- 客户编号名称  
     , order_source_code as order_source_code             --订单来源code
     , order_source_name as order_source_name             --订单来源名称
     
     , null as post_site_num        -- 寄件门店数量
     , null as customer_num         -- 寄件客户数量
     
     , count(waybill_id) as bill_num             -- 订单量	 	 	 
     , sum(if(dt=to_date(pick_time),1,0)) as today_pick_num       -- 当日揽收量	 
     , sum(if(datediff(to_date(pick_time),dt)=1,1,0)) as nextday_pick_num     -- 次日揽收量	 
     , sum(if(pick_time is not null ,1,0)) as total_pick_num       -- 总揽收量	 
     , sum(if(cancel_time is not null ,1,0)) as cancel_num           -- 取消量	 
     , sum(if(pick_time is not null  and pick_time>best_pick_time_end,1,0)) as pick_time_end_num    -- 超时未取件
     , 2 as type                 -- 1网点,2门店,3订单维度	 
     , category_id
     , dt 
from jms_dm.dm_end_sender_report_detail_dt t 
where dt between date_add('{{ execution_date  | cst_ds }}', -4) and '{{ execution_date  | cst_ds }}'  
group by 
       date_time
     , pick_regional_code   -- 取件_大区code
     , pick_regional_name   -- 取件_大区name
     , pick_agent_code      -- 取件_代理区编码
     , pick_agent_name      -- 取件_代理区名称
     , pick_fran_code       -- 取件_加盟商
     , pick_fran_name       -- 取件_加盟商
     , pick_network_code    -- 取件网点code
     , pick_network_name    -- 取件网点名称
     , business_name        -- 品牌
     , post_site_code       -- 门店编码
     , post_site_name       -- 门店name
     , post_site_address    -- 门店地址
     , customer_code        -- 客户编号code
     , customer_name        -- 客户编号名称  
     , dt
     , category_id
     ,order_source_code       --订单来源code
     ,order_source_name       --订单来源名称 
union all 

select 
       date_time
     , pick_regional_code   -- 取件_大区code
     , pick_regional_name   -- 取件_大区name
     , pick_agent_code      -- 取件_代理区编码
     , pick_agent_name      -- 取件_代理区名称
     , pick_fran_code       -- 取件_加盟商
     , pick_fran_name       -- 取件_加盟商
     , pick_network_code    -- 取件网点code
     , pick_network_name    -- 取件网点名称
     , business_name as business_name        -- 品牌
     , null as post_site_code       -- 门店编码
     , null as post_site_name       -- 门店name
     , null as post_site_address    -- 门店地址
     , null as customer_code        -- 客户编号code
     , null as customer_name        -- 客户编号名称  
     ,order_source_code             --订单来源code
     ,order_source_name             --订单来源名称 
     
     , count(distinct post_site_code) as post_site_num        -- 寄件门店数量
     , count(distinct customer_code ) as customer_num         -- 寄件客户数量
     
     , count(waybill_id) as bill_num             -- 订单量	 	 	 
     , sum(if(dt=to_date(pick_time),1,0)) as today_pick_num       -- 当日揽收量	 
     , sum(if(datediff(to_date(pick_time),dt)=1,1,0)) as nextday_pick_num     -- 次日揽收量	 
     , sum(if(pick_time is not null ,1,0)) as total_pick_num       -- 总揽收量	 
     , sum(if(cancel_time is not null ,1,0)) as cancel_num           -- 取消量	 
     , sum(if(pick_time is not null  and pick_time>best_pick_time_end,1,0)) as pick_time_end_num    -- 超时未取件
     , 3 as type                 -- 1网点,2门店,3订单维度	
     , category_id     
     , dt 
from jms_dm.dm_end_sender_report_detail_dt t 
where dt between date_add('{{ execution_date  | cst_ds }}', -4) and '{{ execution_date  | cst_ds }}'  
group by 
      date_time
     ,order_source_code             --订单来源code
     ,order_source_name             --订单来源名称 
     ,business_name
     ,dt
     ,category_id
     , pick_regional_code
     , pick_regional_name
     , pick_agent_code
     , pick_agent_name
     , pick_fran_code
     , pick_fran_name
     , pick_network_code
     , pick_network_name

 distribute by dt, abs(hash(order_source_code)) % 20
;
    